package br.com.massai.model.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Stateless;

import br.com.massai.model.entity.pojo.BudgetItem;
import br.com.massai.model.exceptions.ErrorCore;
import br.com.massai.model.exceptions.SystemException;

@Stateless
public class BudgetItemDAO implements BudgetItemRepository {

	private static final long serialVersionUID = -3063798616962309509L;
	
	@EJB private ConnectionPool connectionPool;

	@Override
	public List<BudgetItem> getBudgetItemByDepartment(String codeSienge) throws SystemException, SQLException, Throwable {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			String query = new StringBuilder("SELECT eorcplanilhaorc.cdobra, eorcplanilhaorc.nuversao, eorcplanilhaorc.cdunidconstobra, eorcplanilhaorc.nusequencial, eorcplanilhaorc.cdtabela, eorcplanilhaorc.cdreferencia, eorcplanilhaorc.deplanilha FROM eorcplanilhaorc, ecadempreend WHERE ecadempreend.cdempreendview = ? AND eorcplanilhaorc.cdobra = ecadempreend.cdobra AND eorcplanilhaorc.nuversao = 1 ORDER BY eorcplanilhaorc.cdreferencia").toString();

			conn = connectionPool.getConnection(ConnectionDB.SIENGE);
			pstm = conn.prepareStatement(query);
			pstm.setString(1, codeSienge);
			rs = pstm.executeQuery();

			List<BudgetItem> items = new ArrayList<>();
			BudgetItem item = null;

			while (rs.next()) {
				item = new BudgetItem();
				item.setCdObra(rs.getInt("cdobra"));
				item.setNuVersao(rs.getInt("nuversao"));
				item.setCdUnidConstObra(rs.getInt("cdunidconstobra"));
				item.setNuSequencial(rs.getInt("nusequencial"));
				item.setCdTabela(rs.getInt("cdtabela"));
				item.setCdReferencia(rs.getString("cdreferencia"));
				item.setDePlanilha(rs.getString("deplanilha"));

				items.add(item);
			}
			return items;

		} catch (SQLException e) {
			throw new SystemException(ErrorCore.ERROR_DATABASE_CONNECTION);
		
		} catch (Throwable e) {
			throw new SystemException(ErrorCore.ERROR_UNEXPECTED);

		} finally {
			if(conn != null) conn.close();
			if(conn != null) conn.close();
			if(conn != null) conn.close();
		}
	}

	@Override
	public List<BudgetItem> getBudgetItemByDepartmentAndDescription(Long codeSienge, String description) throws SystemException, SQLException {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			String query = "SELECT eorcplanilhaorc.cdobra, eorcplanilhaorc.nuversao, eorcplanilhaorc.cdunidconstobra, eorcplanilhaorc.nusequencial, eorcplanilhaorc.cdtabela, eorcplanilhaorc.cdreferencia, eorcplanilhaorc.deplanilha FROM eorcplanilhaorc, ecadempreend WHERE ecadempreend.cdempreendview = ? AND eorcplanilhaorc.cdobra = ecadempreend.cdobra AND LOWER(eorcplanilhaorc.deplanilha) LIKE LOWER(?) AND eorcplanilhaorc.nuversao = 1 AND eorcplanilhaorc.cdtabela > 0 ORDER BY eorcplanilhaorc.cdreferencia";

			conn = connectionPool.getConnection(ConnectionDB.SIENGE);
			pstm = conn.prepareStatement(query);
			pstm.setLong(1, codeSienge);
			pstm.setString(2, description == null ? "%" : "%"+description+"%");
			rs = pstm.executeQuery();

			List<BudgetItem> items = new ArrayList<>();
			BudgetItem item = null;

			while (rs.next()) {
				item = new BudgetItem();
				item.setCdObra(rs.getInt("cdobra"));
				item.setNuVersao(rs.getInt("nuversao"));
				item.setCdUnidConstObra(rs.getInt("cdunidconstobra"));
				item.setNuSequencial(rs.getInt("nusequencial"));
				item.setCdTabela(rs.getInt("cdtabela"));
				item.setCdReferencia(rs.getString("cdreferencia"));
				item.setDePlanilha(rs.getString("deplanilha"));

				items.add(item);
			}
			return items;

		} catch (SQLException e) {
			throw new SystemException(ErrorCore.ERROR_DATABASE_CONNECTION);

		} catch (Exception e) {
			throw new SystemException(ErrorCore.ERROR_LOADING_BUDGEITEM);

		} finally {
			if(conn != null) conn.close();
			if(conn != null) conn.close();
			if(conn != null) conn.close();
		}
	}
}